package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.config.annotation.DataScope;
import com.dragon.repository.entity.PlanCollect;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 计划回款表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-06-30
 */
@Mapper
public interface PlanCollectMapper extends BaseMapper<PlanCollect> {

    @Select("<script>" +
            "SELECT M.* From (" +
            "SELECT a.*,b.name AS tractName,b.num contractCode,b.modeling, " +
            "dc.customer_name as customerName, b.admin_id as contractCreateId, b.department_id as contractDepartmentId , b.order_time,dp.deliver_status deliverStatus,dp.type " +
            "FROM dragon_plancollect a " +
            "JOIN dragon_contract b ON a.contract_id = b.id and b.status = 1 and b.deleted = 0 " +
            "LEFT JOIN dragon_customer dc ON b.customer_id = dc.id " +
            "LEFT JOIN dragon_produce dp on a.contract_id = dp.contract_id and dp.status = 1 ) M " +
            "${ew.customSqlSegment} " +
            "</script>")
    @DataScope(adminAlias = "M", adminField = "admin_id,contractCreateId", deptAlias = "M", deptField = "department_id,contractDepartmentId", authCodeString = "plancollect")
    Page<PlanCollect> planList(Page<PlanCollect> plaPage, @Param(Constants.WRAPPER) QueryWrapper<PlanCollect> wrapper);

    /**
     * 根据合同获取计划回款列表
     * @param contractId
     * @return
     */
    @Select("SELECT M.* From (" +
            "SELECT a.*,b.`name` AS tractName  FROM dragon_plancollect a " +
            "LEFT JOIN dragon_contract b ON a.contract_id = b.id )M WHERE 1=1 " +
            " AND M.contract_id = #{contractId} ")
    List<PlanCollect> selectListByContractId(Integer contractId);

    /**
     * 应收总
     * @return
     */
    @Select("SELECT SUM(dp.plan_money) FROM dragon_plancollect dp LEFT JOIN dragon_contract dc on dp.contract_id = dc.id " +
            "WHERE dp.deleted = 0 and dc.deleted = 0 and dc.status = 1 ")
    @DataScope(adminAlias = "dp", adminField = "admin_id", deptAlias = "dp", deptField = "department_id", authCodeString = "plancollect")
    Double selectYearPlanMoney();

    /**
     * 应收年总
     * @return
     */
    @Select("SELECT SUM(dp.plan_money) FROM dragon_plancollect dp LEFT JOIN dragon_contract dc on dp.contract_id = dc.id " +
            "WHERE dp.deleted = 0 and dc.deleted = 0 and dc.status = 1 and left(dc.order_time, 4) = #{time} ")
    @DataScope(adminAlias = "dp", adminField = "admin_id", deptAlias = "dp", deptField = "department_id", authCodeString = "plancollect")
    Double selectTotalPlanMoney(String time);

    /**
     * 应收月
     * @return
     */
    @Select("SELECT SUM(dp.plan_money) FROM dragon_plancollect dp LEFT JOIN dragon_contract dc on dp.contract_id = dc.id " +
            "WHERE dp.deleted = 0 and dc.deleted = 0 and dc.status = 1 and LEFT(dc.order_time,7) = LEFT(NOW(),7)")
    @DataScope(adminAlias = "dp", adminField = "admin_id", deptAlias = "dp", deptField = "department_id", authCodeString = "plancollect")
    Double selectMonthPlanMoney();


    @Select("SELECT M.* From (" +
            "SELECT a.*,b.order_time from dragon_plancollect a " +
            " JOIN dragon_contract b ON a.contract_id = b.id and b.stauts = 1 and b.deleted = 0 )M " +
            " ${ew.customSqlSegment}")
    List<PlanCollect> selectPlanByOrderTime(@Param(Constants.WRAPPER) QueryWrapper<PlanCollect> wrapper);

    @Select("SELECT M.* From (" +
            "SELECT a.*,b.order_time from dragon_plancollect a " +
            " LEFT JOIN dragon_contract b ON a.contract_id = b.id  " +
            " LEFT JOIN dragon_payment_contract c on a.id = c.plan_id " +
            " LEFT JOIN dragon_customer_payment d on d.id = c.payment_id " +
            " where d.payment_status = 0 and d.status = 1 )M" +
            " ${ew.customSqlSegment}")
    List<PlanCollect> selectPaymentIds(@Param(Constants.WRAPPER) QueryWrapper<PlanCollect> wrapper);


    /**
     * 应收 年金额
     * @param year
     * @return
     */
    @Select("SELECT SUM(dp.plan_money) FROM dragon_plancollect dp LEFT JOIN dragon_contract dc on dp.contract_id = dc.id " +
            "WHERE dp.deleted = 0 and dc.deleted = 0 and dc.status = 1 and LEFT(dc.order_time,4) = #{year} ")
    @DataScope(adminAlias = "dp", adminField = "admin_id", deptAlias = "dp", deptField = "department_id", authCodeString = "plancollect")
    Double selectMoneyForYear(String year);

    @Select("with base as(" +
            "    select b.order_time,a.plan_money,a.refund_money from dragon_plancollect a left join dragon_contract b on a.contract_id = b.id " +
            "    where b.status = 1 and b.deleted=0 and a.deleted = 0" +
            ")," +
            "totalMoney as (" +
            "    select sum(plan_money-refund_money) totalPlanMoney from base" +
            ")," +
            "yearMoney as (" +
            "    select sum(plan_money-refund_money) yearPlanMoney from base where left(order_time,4) = left(now(),4)" +
            ")," +
            "monthMoney as (" +
            "    select sum(plan_money-refund_money) monthPlanMoney from base where left(order_time,7) = left(now(),7)" +
            ")," +
            "makeData as (" +
            "    select a.totalPlanMoney,b.yearPlanMoney,c.monthPlanMoney from totalMoney a,yearMoney b,monthMoney c" +
            ")" +
            "select * from makeData")
    Map<String,Object> selectListStatistics();

    /**
     * 应收月总额统计
     * @param time
     * @return
     */
    @Select("select " +
            "    IFNULL(sum(case month(dp.create_time) when '1'  then dp.plan_money else 0 end),0) as '1月', " +
            "    IFNULL(sum(case month(dp.create_time) when '2'  then dp.plan_money else 0 end),0) as '2月', " +
            "    IFNULL(sum(case month(dp.create_time) when '3'  then dp.plan_money else 0 end),0) as '3月', " +
            "    IFNULL(sum(case month(dp.create_time) when '4'  then dp.plan_money else 0 end),0) as '4月', " +
            "    IFNULL(sum(case month(dp.create_time) when '5'  then dp.plan_money else 0 end),0) as '5月', " +
            "    IFNULL(sum(case month(dp.create_time) when '6'  then dp.plan_money else 0 end),0) as '6月', " +
            "    IFNULL(sum(case month(dp.create_time) when '7'  then dp.plan_money else 0 end),0) as '7月', " +
            "    IFNULL(sum(case month(dp.create_time) when '8'  then dp.plan_money else 0 end),0) as '8月', " +
            "    IFNULL(sum(case month(dp.create_time) when '9'  then dp.plan_money else 0 end),0) as '9月', " +
            "    IFNULL(sum(case month(dp.create_time) when '10' then dp.plan_money  else 0 end),0) as '10月', " +
            "    IFNULL(sum(case month(dp.create_time) when '11' then dp.plan_money  else 0 end),0) as '11月', " +
            "    IFNULL(sum(case month(dp.create_time) when '12' then dp.plan_money  else 0 end),0) as '12月' " +
            "FROM dragon_plancollect dp LEFT JOIN dragon_contract dc on dp.contract_id = dc.id  " +
            "            WHERE dp.deleted = 0 and dc.deleted = 0 and dc.status = 1 and YEAR(dc.order_time) = #{time} ")
    @DataScope(adminAlias = "dp", adminField = "admin_id", deptAlias = "dp", deptField = "department_id", authCodeString = "plancollect")
    LinkedHashMap<String, Object> planMonthlyStatistics(String time);

    /**
     * 实收应收月统计
     * @return
     */
    @Select("WITH base AS ( " +
            " SELECT dp.create_time, dp.plan_money  " +
            " FROM dragon_plancollect dp " +
            " LEFT JOIN dragon_contract dc ON dp.contract_id = dc.id  " +
            " WHERE dp.deleted = 0 AND dc.deleted = 0 AND dc.STATUS = 1 " +
            " AND YEAR ( dc.order_time ) = YEAR (NOW()) " +
            ")," +
            "planBase as ( " +
            "SELECT " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '1' THEN plan_money ELSE 0 END ), 0 ) AS Jan, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '2' THEN plan_money ELSE 0 END ), 0 ) AS Feb, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '3' THEN plan_money ELSE 0 END ), 0 ) AS Mar, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '4' THEN plan_money ELSE 0 END ), 0 ) AS Apr, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '5' THEN plan_money ELSE 0 END ), 0 ) AS May, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '6' THEN plan_money ELSE 0 END ), 0 ) AS June, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '7' THEN plan_money ELSE 0 END ), 0 ) AS July, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '8' THEN plan_money ELSE 0 END ), 0 ) AS Aug, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '9' THEN plan_money ELSE 0 END ), 0 ) AS Sept, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '10' THEN plan_money ELSE 0 END ), 0 ) AS Oct, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '11' THEN plan_money ELSE 0 END ), 0 ) AS Nov, " +
            " IFNULL( sum( CASE MONTH ( create_time ) WHEN '12' THEN plan_money ELSE 0 END ), 0 ) AS Dece FROM base " +
            "), " +
            "customerPaymentBase as( " +
            "SELECT " +
            "  IFNULL(SUM(case month(create_time) when '1'  then money else 0 end),0) as Jan, " +
            "  IFNULL(sum(case month(create_time) when '2'  then money else 0 end),0) as Feb, " +
            "  IFNULL(sum(case month(create_time) when '3'  then money else 0 end),0) as Mar, " +
            "  IFNULL(sum(case month(create_time) when '4'  then money else 0 end),0) as Apr, " +
            "  IFNULL(sum(case month(create_time) when '5'  then money else 0 end),0) as May, " +
            "  IFNULL(sum(case month(create_time) when '6'  then money else 0 end),0) as June, " +
            "  IFNULL(sum(case month(create_time) when '7'  then money else 0 end),0) as July, " +
            "  IFNULL(sum(case month(create_time) when '8'  then money else 0 end),0) as Aug, " +
            "  IFNULL(sum(case month(create_time) when '9'  then money else 0 end),0) as Sept, " +
            "  IFNULL(sum(case month(create_time) when '10' then money  else 0 end),0) as Oct, " +
            "  IFNULL(sum(case month(create_time) when '11' then money  else 0 end),0) as Nov,  " +
            "  IFNULL(sum(case month(create_time) when '12' then money  else 0 end),0) as Dece  " +
            "  FROM dragon_customer_payment  where status = 1 and payment_status = 1 AND YEAR(create_time) = YEAR(NOW()) ) " +
            "select a.* from planBase a " +
            "UNION all " +
            "SELECT b.* from customerPaymentBase b ")
    List<LinkedHashMap<String, Object>> planCustomerPaymentStatist();
}
